#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
简单图形化ETL工具
作者: AI Assistant
日期: 2024-06-22
功能: 数据提取、转换、加载的图形化界面
"""

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import pandas as pd
import numpy as np
from datetime import datetime
import os
import json

class SimpleETLTool:
    def __init__(self, root):
        self.root = root
        self.root.title("简单ETL工具 - 数据处理专家")
        self.root.geometry("1200x800")
        
        # 数据存储
        self.data = None
        self.original_data = None
        self.processed_data = None
        
        # 创建界面
        self.create_widgets()
        
    def create_widgets(self):
        """创建主界面"""
        # 主框架
        main_frame = ttk.Frame(self.root, padding="10")
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 配置网格权重
        self.root.columnconfigure(0, weight=1)
        self.root.rowconfigure(0, weight=1)
        main_frame.columnconfigure(1, weight=1)
        main_frame.rowconfigure(2, weight=1)
        
        # 标题
        title_label = ttk.Label(main_frame, text="🔧 简单ETL数据处理工具", 
                               font=('Arial', 16, 'bold'))
        title_label.grid(row=0, column=0, columnspan=3, pady=(0, 20))
        
        # 左侧控制面板
        control_frame = ttk.LabelFrame(main_frame, text="📋 操作面板", padding="10")
        control_frame.grid(row=1, column=0, rowspan=2, sticky=(tk.W, tk.E, tk.N, tk.S), padx=(0, 10))
        
        # 数据加载部分
        load_frame = ttk.LabelFrame(control_frame, text="📂 数据加载", padding="5")
        load_frame.pack(fill="x", pady=(0, 10))
        
        ttk.Button(load_frame, text="选择CSV文件", command=self.load_csv).pack(fill="x", pady=2)
        ttk.Button(load_frame, text="选择Excel文件", command=self.load_excel).pack(fill="x", pady=2)
        
        # 数据信息显示
        self.info_text = tk.Text(load_frame, height=4, width=30)
        self.info_text.pack(fill="x", pady=5)
        
        # 数据清洗部分
        clean_frame = ttk.LabelFrame(control_frame, text="🧹 数据清洗", padding="5")
        clean_frame.pack(fill="x", pady=(0, 10))
        
        ttk.Button(clean_frame, text="删除空值", command=self.remove_nulls).pack(fill="x", pady=2)
        ttk.Button(clean_frame, text="删除重复值", command=self.remove_duplicates).pack(fill="x", pady=2)
        ttk.Button(clean_frame, text="标准化文本", command=self.normalize_text).pack(fill="x", pady=2)
        ttk.Button(clean_frame, text="填充缺失值", command=self.fill_missing).pack(fill="x", pady=2)
        
        # 数据转换部分
        transform_frame = ttk.LabelFrame(control_frame, text="🔄 数据转换", padding="5")
        transform_frame.pack(fill="x", pady=(0, 10))
        
        ttk.Button(transform_frame, text="添加计算列", command=self.add_calculated_column).pack(fill="x", pady=2)
        ttk.Button(transform_frame, text="数据类型转换", command=self.convert_types).pack(fill="x", pady=2)
        ttk.Button(transform_frame, text="数据分组统计", command=self.group_statistics).pack(fill="x", pady=2)
        
        # 数据导出部分
        export_frame = ttk.LabelFrame(control_frame, text="💾 数据导出", padding="5")
        export_frame.pack(fill="x", pady=(0, 10))
        
        ttk.Button(export_frame, text="导出CSV", command=self.export_csv).pack(fill="x", pady=2)
        ttk.Button(export_frame, text="导出Excel", command=self.export_excel).pack(fill="x", pady=2)
        ttk.Button(export_frame, text="生成报告", command=self.generate_report).pack(fill="x", pady=2)
        
        # 操作按钮
        action_frame = ttk.Frame(control_frame)
        action_frame.pack(fill="x", pady=10)
        
        ttk.Button(action_frame, text="🔄 重置数据", command=self.reset_data).pack(fill="x", pady=2)
        ttk.Button(action_frame, text="📊 数据预览", command=self.preview_data).pack(fill="x", pady=2)
        
        # 右侧数据显示区域
        data_frame = ttk.LabelFrame(main_frame, text="📊 数据预览", padding="10")
        data_frame.grid(row=1, column=1, columnspan=2, sticky=(tk.W, tk.E, tk.N, tk.S))
        data_frame.columnconfigure(0, weight=1)
        data_frame.rowconfigure(0, weight=1)
        
        # 创建Treeview来显示数据
        self.tree_frame = ttk.Frame(data_frame)
        self.tree_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        self.tree_frame.columnconfigure(0, weight=1)
        self.tree_frame.rowconfigure(0, weight=1)
        
        # 状态栏
        status_frame = ttk.Frame(main_frame)
        status_frame.grid(row=3, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=(10, 0))
        
        self.status_var = tk.StringVar()
        self.status_var.set("就绪 - 请选择数据文件开始处理")
        ttk.Label(status_frame, textvariable=self.status_var).pack(side=tk.LEFT)
        
    def update_status(self, message):
        """更新状态栏"""
        self.status_var.set(f"{datetime.now().strftime('%H:%M:%S')} - {message}")
        self.root.update()
        
    def load_csv(self):
        """加载CSV文件"""
        file_path = filedialog.askopenfilename(
            title="选择CSV文件",
            filetypes=[("CSV files", "*.csv"), ("All files", "*.*")]
        )
        if file_path:
            try:
                self.data = pd.read_csv(file_path, encoding='utf-8')
                self.original_data = self.data.copy()
                self.update_info()
                self.display_data()
                self.update_status(f"成功加载CSV文件: {os.path.basename(file_path)}")
            except Exception as e:
                messagebox.showerror("错误", f"加载CSV文件失败: {str(e)}")
                
    def load_excel(self):
        """加载Excel文件"""
        file_path = filedialog.askopenfilename(
            title="选择Excel文件",
            filetypes=[("Excel files", "*.xlsx *.xls"), ("All files", "*.*")]
        )
        if file_path:
            try:
                self.data = pd.read_excel(file_path)
                self.original_data = self.data.copy()
                self.update_info()
                self.display_data()
                self.update_status(f"成功加载Excel文件: {os.path.basename(file_path)}")
            except Exception as e:
                messagebox.showerror("错误", f"加载Excel文件失败: {str(e)}")
                
    def update_info(self):
        """更新数据信息显示"""
        if self.data is not None:
            info = f"数据行数: {len(self.data)}\n"
            info += f"数据列数: {len(self.data.columns)}\n"
            info += f"缺失值: {self.data.isnull().sum().sum()}\n"
            info += f"重复行: {self.data.duplicated().sum()}"
            
            self.info_text.delete(1.0, tk.END)
            self.info_text.insert(1.0, info)
            
    def display_data(self):
        """显示数据在Treeview中"""
        # 清除之前的树形视图
        for widget in self.tree_frame.winfo_children():
            widget.destroy()
            
        if self.data is not None:
            # 创建新的Treeview
            columns = list(self.data.columns)
            self.tree = ttk.Treeview(self.tree_frame, columns=columns, show='headings', height=20)
            
            # 设置列标题
            for col in columns:
                self.tree.heading(col, text=col)
                self.tree.column(col, width=100, minwidth=50)
            
            # 插入数据（限制显示前100行）
            for index, row in self.data.head(100).iterrows():
                self.tree.insert('', 'end', values=list(row))
            
            # 添加滚动条
            scrollbar_y = ttk.Scrollbar(self.tree_frame, orient=tk.VERTICAL, command=self.tree.yview)
            scrollbar_x = ttk.Scrollbar(self.tree_frame, orient=tk.HORIZONTAL, command=self.tree.xview)
            self.tree.configure(yscrollcommand=scrollbar_y.set, xscrollcommand=scrollbar_x.set)
            
            # 布局
            self.tree.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
            scrollbar_y.grid(row=0, column=1, sticky=(tk.N, tk.S))
            scrollbar_x.grid(row=1, column=0, sticky=(tk.W, tk.E))
            
    def remove_nulls(self):
        """删除空值"""
        if self.data is not None:
            before_count = len(self.data)
            self.data = self.data.dropna()
            after_count = len(self.data)
            removed = before_count - after_count
            
            self.update_info()
            self.display_data()
            self.update_status(f"删除了 {removed} 行包含空值的数据")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")
            
    def remove_duplicates(self):
        """删除重复值"""
        if self.data is not None:
            before_count = len(self.data)
            self.data = self.data.drop_duplicates()
            after_count = len(self.data)
            removed = before_count - after_count
            
            self.update_info()
            self.display_data()
            self.update_status(f"删除了 {removed} 行重复数据")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")
            
    def normalize_text(self):
        """标准化文本数据"""
        if self.data is not None:
            text_columns = self.data.select_dtypes(include=['object']).columns
            for col in text_columns:
                self.data[col] = self.data[col].astype(str).str.strip().str.lower()
            
            self.display_data()
            self.update_status(f"标准化了 {len(text_columns)} 个文本列")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")
            
    def fill_missing(self):
        """填充缺失值"""
        if self.data is not None:
            # 数值列用均值填充
            numeric_columns = self.data.select_dtypes(include=[np.number]).columns
            for col in numeric_columns:
                self.data[col].fillna(self.data[col].mean(), inplace=True)
            
            # 文本列用众数填充
            text_columns = self.data.select_dtypes(include=['object']).columns
            for col in text_columns:
                mode_value = self.data[col].mode()
                if len(mode_value) > 0:
                    self.data[col].fillna(mode_value[0], inplace=True)
            
            self.update_info()
            self.display_data()
            self.update_status("缺失值填充完成")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")
            
    def add_calculated_column(self):
        """添加计算列"""
        if self.data is not None:
            # 简单示例：添加行号列
            self.data['行号'] = range(1, len(self.data) + 1)
            
            # 如果有数值列，添加一个总和列示例
            numeric_columns = self.data.select_dtypes(include=[np.number]).columns
            if len(numeric_columns) > 1:
                self.data['数值列总和'] = self.data[numeric_columns].sum(axis=1)
            
            self.display_data()
            self.update_status("添加了计算列")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")
            
    def convert_types(self):
        """数据类型转换"""
        if self.data is not None:
            # 尝试将可能的数值列转换为数值类型
            for col in self.data.columns:
                try:
                    self.data[col] = pd.to_numeric(self.data[col], errors='ignore')
                except:
                    pass
            
            self.display_data()
            self.update_status("数据类型转换完成")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")
            
    def group_statistics(self):
        """数据分组统计"""
        if self.data is not None:
            # 创建新窗口显示统计信息
            stats_window = tk.Toplevel(self.root)
            stats_window.title("数据统计信息")
            stats_window.geometry("800x600")
            
            # 创建文本框显示统计信息
            stats_text = tk.Text(stats_window, wrap=tk.WORD)
            stats_text.pack(fill="both", expand=True, padx=10, pady=10)
            
            # 生成统计信息
            stats_info = "=== 数据统计报告 ===\n\n"
            stats_info += f"数据概览:\n{self.data.describe()}\n\n"
            stats_info += f"数据类型:\n{self.data.dtypes}\n\n"
            stats_info += f"缺失值统计:\n{self.data.isnull().sum()}\n\n"
            
            stats_text.insert(1.0, stats_info)
            self.update_status("生成了数据统计信息")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")
            
    def export_csv(self):
        """导出CSV文件"""
        if self.data is not None:
            file_path = filedialog.asksaveasfilename(
                title="保存CSV文件",
                defaultextension=".csv",
                filetypes=[("CSV files", "*.csv"), ("All files", "*.*")]
            )
            if file_path:
                try:
                    self.data.to_csv(file_path, index=False, encoding='utf-8-sig')
                    self.update_status(f"成功导出CSV文件: {os.path.basename(file_path)}")
                    messagebox.showinfo("成功", "CSV文件导出成功！")
                except Exception as e:
                    messagebox.showerror("错误", f"导出CSV文件失败: {str(e)}")
        else:
            messagebox.showwarning("警告", "没有数据可导出")
            
    def export_excel(self):
        """导出Excel文件"""
        if self.data is not None:
            file_path = filedialog.asksaveasfilename(
                title="保存Excel文件",
                defaultextension=".xlsx",
                filetypes=[("Excel files", "*.xlsx"), ("All files", "*.*")]
            )
            if file_path:
                try:
                    self.data.to_excel(file_path, index=False)
                    self.update_status(f"成功导出Excel文件: {os.path.basename(file_path)}")
                    messagebox.showinfo("成功", "Excel文件导出成功！")
                except Exception as e:
                    messagebox.showerror("错误", f"导出Excel文件失败: {str(e)}")
        else:
            messagebox.showwarning("警告", "没有数据可导出")
            
    def generate_report(self):
        """生成处理报告"""
        if self.data is not None:
            file_path = filedialog.asksaveasfilename(
                title="保存处理报告",
                defaultextension=".txt",
                filetypes=[("Text files", "*.txt"), ("All files", "*.*")]
            )
            if file_path:
                try:
                    report = f"ETL数据处理报告\n"
                    report += f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n"
                    report += f"=" * 50 + "\n\n"
                    
                    if self.original_data is not None:
                        report += f"原始数据行数: {len(self.original_data)}\n"
                        report += f"原始数据列数: {len(self.original_data.columns)}\n\n"
                    
                    report += f"处理后数据行数: {len(self.data)}\n"
                    report += f"处理后数据列数: {len(self.data.columns)}\n\n"
                    
                    report += f"数据类型分布:\n{self.data.dtypes.value_counts()}\n\n"
                    report += f"缺失值统计:\n{self.data.isnull().sum()}\n\n"
                    report += f"数据概览:\n{self.data.describe()}\n"
                    
                    with open(file_path, 'w', encoding='utf-8') as f:
                        f.write(report)
                    
                    self.update_status(f"成功生成处理报告: {os.path.basename(file_path)}")
                    messagebox.showinfo("成功", "处理报告生成成功！")
                except Exception as e:
                    messagebox.showerror("错误", f"生成报告失败: {str(e)}")
        else:
            messagebox.showwarning("警告", "没有数据可生成报告")
            
    def reset_data(self):
        """重置数据到原始状态"""
        if self.original_data is not None:
            self.data = self.original_data.copy()
            self.update_info()
            self.display_data()
            self.update_status("数据已重置到原始状态")
        else:
            messagebox.showwarning("警告", "没有原始数据可重置")
            
    def preview_data(self):
        """数据预览"""
        if self.data is not None:
            self.display_data()
            self.update_status("数据预览已更新")
        else:
            messagebox.showwarning("警告", "请先加载数据文件")

def main():
    """主函数"""
    root = tk.Tk()
    app = SimpleETLTool(root)
    root.mainloop()

if __name__ == "__main__":
    main() 